import { query } from '@/utils/db';
import { RowDataPacket, ResultSetHeader } from 'mysql2';

export interface Policy {
  id?: number;
  title: string;
  summary: string;
  content?: string;
  source: string;
  publishDate: string | null;
  originalUrl: string;
  category: string;
  tags: string[];
  keyPoints: string[];
  requirements: string[];
  createdAt?: Date;
  updatedAt?: Date;
}

export interface PolicyListItem {
  id: number;
  title: string;
  summary: string;
  source: string;
  publishDate: string | null;
  url: string;
  category: string;
  tags: string[];
}

export interface PolicyListResult {
  policies: PolicyListItem[];
  page: number;
  limit: number;
  total: number;
  totalPages: number;
}

/**
 * 获取政策列表
 */
export async function getPolicies(
  page: number = 1,
  limit: number = 10,
  category?: string,
  search?: string
): Promise<PolicyListResult> {
  const offset = (page - 1) * limit;
  
  // 构建查询条件
  let whereClause = '1=1';
  const params: any[] = [];
  
  if (category && category !== '全部') {
    whereClause += ' AND category = ?';
    params.push(category);
  }
  
  if (search) {
    whereClause += ' AND (title LIKE ? OR summary LIKE ?)';
    params.push(`%${search}%`, `%${search}%`);
  }
  
  // 查询政策列表
  const policiesQuery = `
    SELECT 
      id, title, summary, source, publish_date, 
      original_url, category, tags
    FROM enterprise_policies
    WHERE ${whereClause}
    ORDER BY publish_date DESC, id DESC
    LIMIT ? OFFSET ?
  `;
  
  // 查询总数
  const countQuery = `
    SELECT COUNT(*) as total
    FROM enterprise_policies
    WHERE ${whereClause}
  `;
  
  // 执行查询
  const policies = await query<RowDataPacket[]>(policiesQuery, [...params, limit, offset]);
  const [countResult] = await query<RowDataPacket[]>(countQuery, params);
  
  const total = countResult[0].total;
  const totalPages = Math.ceil(total / limit);
  
  // 处理数据
  const formattedPolicies: PolicyListItem[] = policies.map(policy => {
    return {
      id: policy.id,
      title: policy.title,
      summary: policy.summary,
      source: policy.source,
      publishDate: policy.publish_date ? new Date(policy.publish_date).toISOString().split('T')[0] : null,
      url: policy.original_url,
      category: policy.category,
      tags: policy.tags ? policy.tags.split(',') : []
    };
  });
  
  return {
    policies: formattedPolicies,
    page,
    limit,
    total,
    totalPages
  };
}

/**
 * 获取政策详情
 */
export async function getPolicyById(id: number): Promise<Policy | null> {
  const policyQuery = `
    SELECT 
      id, title, summary, content, source, publish_date, 
      original_url, category, tags, key_points, requirements,
      created_at, updated_at
    FROM enterprise_policies
    WHERE id = ?
  `;
  
  const [policies] = await query<RowDataPacket[]>(policyQuery, [id]);
  
  if (!policies || policies.length === 0) {
    return null;
  }
  
  const policy = policies[0];
  
  return {
    id: policy.id,
    title: policy.title,
    summary: policy.summary,
    content: policy.content,
    source: policy.source,
    publishDate: policy.publish_date ? new Date(policy.publish_date).toISOString().split('T')[0] : null,
    originalUrl: policy.original_url,
    category: policy.category,
    tags: policy.tags ? policy.tags.split(',') : [],
    keyPoints: policy.key_points ? JSON.parse(policy.key_points) : [],
    requirements: policy.requirements ? JSON.parse(policy.requirements) : [],
    createdAt: policy.created_at,
    updatedAt: policy.updated_at
  };
}

/**
 * 获取相关政策
 */
export async function getRelatedPolicies(policyId: number, limit: number = 5): Promise<PolicyListItem[]> {
  const relatedPoliciesQuery = `
    SELECT 
      p.id, p.title, p.summary, p.source, p.publish_date, 
      p.original_url, p.category, p.tags
    FROM enterprise_policy_relations r
    JOIN enterprise_policies p ON r.related_policy_id = p.id
    WHERE r.policy_id = ?
    UNION
    SELECT 
      p.id, p.title, p.summary, p.source, p.publish_date, 
      p.original_url, p.category, p.tags
    FROM enterprise_policy_relations r
    JOIN enterprise_policies p ON r.policy_id = p.id
    WHERE r.related_policy_id = ?
    LIMIT ?
  `;
  
  const relatedPolicies = await query<RowDataPacket[]>(relatedPoliciesQuery, [policyId, policyId, limit]);
  
  const formattedPolicies: PolicyListItem[] = relatedPolicies.map(policy => {
    return {
      id: policy.id,
      title: policy.title,
      summary: policy.summary,
      source: policy.source,
      publishDate: policy.publish_date ? new Date(policy.publish_date).toISOString().split('T')[0] : null,
      url: policy.original_url,
      category: policy.category,
      tags: policy.tags ? policy.tags.split(',') : []
    };
  });
  
  return formattedPolicies;
}

/**
 * 获取政策分类列表
 */
export async function getPolicyCategories(): Promise<string[]> {
  const categoryQuery = `
    SELECT DISTINCT category 
    FROM enterprise_policies 
    WHERE category IS NOT NULL AND category != ''
    ORDER BY category
  `;
  
  const categories = await query<RowDataPacket[]>(categoryQuery);
  
  return categories.map((item: RowDataPacket) => item.category);
} 